***************************************** Data Type Casting Functions and Operators ***************************************** CAST ==== .. function:: CAST (cast_operand AS cast_target) The **CAST** operator can be used to explicitly cast one data type to another in the **SELECT** statement. A query list or a value expression in the **WHERE** clause can be cast to another data type. :param cast_operand: Declares the value to cast to a different data type. :param cast_target: Specifies the type to cast to. :rtype: cast_target Depending on the situation, data type can be automatically converted without suing the **CAST** operator. For details, see :ref:`implicit-type-conversion`. See :ref:`cast-string-to-datetime` regarding to convert the string of date/time type into date/time type. The following table shows a summary of explicit type conversions (casts) using the **CAST** operator in CUBRID. +----------------+--------+--------+--------+--------+--------+--------+----------+----------+-------+-------+--------+--------+-------+--------+--------+ | **From \\ To** | **EN** | **AN** | **VC** | **FC** | **VB** | **FB** | **BLOB** | **CLOB** | **D** | **T** | **UT** | **DT** | **S** | **MS** | **SQ** | +----------------+--------+--------+--------+--------+--------+--------+----------+----------+-------+-------+--------+--------+-------+--------+--------+ | **EN** | Yes | Yes | Yes | Yes | No | No | No | No | No | No | No | No | No | No | No | +----------------+--------+--------+--------+--------+--------+--------+----------+----------+-------+-------+--------+--------+-------+--------+--------+ | **AN** | Yes | Yes | Yes | Yes | No | No | No | No | No | No | No | No | No | No | No | +----------------+--------+--------+--------+--------+--------+--------+----------+----------+-------+-------+--------+--------+-------+--------+--------+ | **VC** | Yes | Yes | Yes* | Yes* | Yes | Yes | Yes | Yes | Yes | Yes | Yes | Yes | No | No | No | +----------------+--------+--------+--------+--------+--------+--------+----------+----------+-------+-------+--------+--------+-------+--------+--------+ | **FC** | Yes | Yes | Yes* | Yes* | Yes | Yes | Yes | Yes | Yes | Yes | Yes | Yes | No | No | No | +----------------+--------+--------+--------+--------+--------+--------+----------+----------+-------+-------+--------+--------+-------+--------+--------+ | **VB** | No | No | Yes | Yes | Yes | Yes | Yes | Yes | No | No | No | No | No | No | No | +----------------+--------+--------+--------+--------+--------+--------+----------+----------+-------+-------+--------+--------+-------+--------+--------+ | **FB** | No | No | Yes | Yes | Yes | Yes | Yes | Yes | No | No | No | No | No | No | No | +----------------+--------+--------+--------+--------+--------+--------+----------+----------+-------+-------+--------+--------+-------+--------+--------+ | **BLOB** | No | No | No | No | Yes | Yes | Yes | No | No | No | No | No | No | No | No | +----------------+--------+--------+--------+--------+--------+--------+----------+----------+-------+-------+--------+--------+-------+--------+--------+ | **CLOB** | No | No | Yes | Yes | No | No | No | Yes | No | No | No | No | No | No | No | +----------------+--------+--------+--------+--------+--------+--------+----------+----------+-------+-------+--------+--------+-------+--------+--------+ | **D** | No | No | Yes | Yes | No | No | No | No | Yes | No | Yes | Yes | No | No | No | +----------------+--------+--------+--------+--------+--------+--------+----------+----------+-------+-------+--------+--------+-------+--------+--------+ | **T** | No | No | Yes | Yes | No | No | No | No | No | Yes | No | No | No | No | No | +----------------+--------+--------+--------+--------+--------+--------+----------+----------+-------+-------+--------+--------+-------+--------+--------+ | **UT** | No | No | Yes | Yes | No | No | No | No | Yes | Yes | Yes | Yes | No | No | No | +----------------+--------+--------+--------+--------+--------+--------+----------+----------+-------+-------+--------+--------+-------+--------+--------+ | **DT** | No | No | Yes | Yes | No | No | No | No | Yes | Yes | Yes | Yes | No | No | No | +----------------+--------+--------+--------+--------+--------+--------+----------+----------+-------+-------+--------+--------+-------+--------+--------+ | **S** | No | No | No | No | No | No | No | No | No | No | No | No | Yes | Yes | Yes | +----------------+--------+--------+--------+--------+--------+--------+----------+----------+-------+-------+--------+--------+-------+--------+--------+ | **MS** | No | No | No | No | No | No | No | No | No | No | No | No | Yes | Yes | Yes | +----------------+--------+--------+--------+--------+--------+--------+----------+----------+-------+-------+--------+--------+-------+--------+--------+ | **SQ** | No | No | No | No | No | No | No | No | No | No | No | No | Yes | Yes | Yes | +----------------+--------+--------+--------+--------+--------+--------+----------+----------+-------+-------+--------+--------+-------+--------+--------+ * The **CAST** operation is allowed only when the value expression and the data type to be cast have the same character set. * **Data Type Key** * **EN** : Exact numeric data type (**INTEGER**, **SMALLINT**, **BIGINT**, **NUMERIC**, **DECIMAL**) * **AN** : Approximate numeric data type (**FLOAT/REAL**, **DOUBLE**) * **VC** : Variable-length character string (**VARCHAR** (*n*)) * **FC** : Fixed-length character string (**CHAR** (*n*)) * **VB** : Variable-length bit string (**BIT VARYING** (*n*)) * **FB** : Fixed-length bit string (**BIT** (*n*)) * **BLOB** : Binary data that is stored outside DB * **CLOB** : String data that is stored inside DB * **D** : **DATE** * **T** : **TIME** * **DT** : **DATETIME** * **UT** : **TIMESTAMP** * **S** : **SET** * **MS** : **MULTISET** * **SQ** : **LIST** (= **SEQUENCE**) .. code-block:: sql --operation after casting character as INT type returns 2 SELECT (1+CAST ('1' AS INT)); :: (1+ cast('1' as integer)) =========================== 2 .. code-block:: sql --cannot cast the string which is out of range as SMALLINT SELECT (1+CAST('1234567890' AS SMALLINT)); :: ERROR: Cannot coerce value of domain "character" to domain "smallint". .. code-block:: sql --operation after casting returns 1+1234567890 SELECT (1+CAST('1234567890' AS INT)); :: (1+ cast('1234567890' as integer)) ==================================== 1234567891 .. code-block:: sql --'1234.567890' is casted to 1235 after rounding up SELECT (1+CAST('1234.567890' AS INT)); :: (1+ cast('1234.567890' as integer)) ==================================== 1236 .. code-block:: sql --'1234.567890' is casted to string containing only first 5 letters. SELECT (CAST('1234.567890' AS CHAR(5))); :: ( cast('1234.567890' as char(5))) ==================================== '1234.' .. code-block:: sql --numeric type can be casted to CHAR type only when enough length is specified SELECT (CAST(1234.567890 AS CHAR(5))); :: ERROR: Cannot coerce value of domain "numeric" to domain "character". .. code-block:: sql --numeric type can be casted to CHAR type only when enough length is specified SELECT (CAST(1234.567890 AS CHAR(11))); :: ( cast(1234.567890 as char(11))) ==================================== '1234.567890' .. code-block:: sql --numeric type can be casted to CHAR type only when enough length is specified SELECT (CAST(1234.567890 AS VARCHAR)); :: ( cast(1234.567890 as varchar)) ==================================== '1234.567890' .. code-block:: sql --string can be casted to time/date types only when its literal is correctly specified SELECT (CAST('2008-12-25 10:30:20' AS TIMESTAMP)); :: ( cast('2008-12-25 10:30:20' as timestamp)) ============================================= 10:30:20 AM 12/25/2008 .. code-block:: sql SELECT (CAST('10:30:20' AS TIME)); :: ( cast('10:30:20' as time)) ================================================== 10:30:20 AM .. code-block:: sql --string can be casted to TIME type when its literal is same as TIME's. SELECT (CAST('2008-12-25 10:30:20' AS TIME)); :: ( cast('2008-12-25 10:30:20' as time)) ======================================== 10:30:20 AM .. code-block:: sql --string can be casted to TIME type after specifying its type of the string SELECT (CAST(TIMESTAMP'2008-12-25 10:30:20' AS TIME)); :: ( cast(timestamp '2008-12-25 10:30:20' as time)) ================================================== 10:30:20 AM .. code-block:: sql SELECT CAST('abcde' AS BLOB); :: cast('abcde' as blob) ====================== file:/home1/user1/db/tdb/lob/ces_743/ces_temp.00001283232024309172_1342 .. code-block:: sql SELECT CAST(B'11010000' as varchar(10)); :: cast(B'11010000' as varchar(10)) ==================================== 'd0' .. code-block:: sql SELECT CAST('1A' AS BLOB); :: cast('1A' as bit(16)) ================================= X'1a00' .. note:: * **CAST** is allowed only between data types having the same character set. * If you cast an approximate data type(FLOAT, DOUBLE) to integer type, the number is rounded to zero decimal places. * If you cast an exact numeric data type(NUMERIC) to integer type, the number is rounded to zero decimal places. * If you cast a numeric data type to string character type, it should be longer than the length of significant figures + decimal point. An error occurs otherwise. * If you cast a character string type *A* to a character string type *B*, B should be longer than the *A*. The end of character string is truncated otherwise. * If you cast a character string type *A* to a date-time date type *B*, it is converted only when literal of *A* and *B* type match one another. An error occurs otherwise. * You must explicitly do type casting for numeric data stored in a character string so that an arithmetic operation can be performed. DATE_FORMAT =========== .. function:: DATE_FORMAT (date, format) The **DATE_FORMAT** function converts the value of strings with **DATE** format ('*YYYY*-*MM*-*DD*' or '*MM*/*DD*/*YYYY*') or that of date/time data type (**DATE**, **TIMESTAMP**, **DATETIME**) to specified date/time format and then return the value with the **VARCHAR** data type. For the format parameter to assign, refer to :ref:`Date/Time Format 2 ` table of the :func:`DATE_FORMAT`. The :ref:`Date/Time Format 2 ` table is used in :func:`DATE_FORMAT`, :func:`TIME_FORMAT`, and :func:`STR_TO_DATE` functions. :param date: A value of strings with the **DATE** format ('*YYYY*-*MM*-*DD*' or '*MM*/*DD*/*YYYY*') or that of date/time data type (**DATE**, **TIMESTAMP**, **DATETIME**) can be specified . :param format: Specifies the output format. The format specifier starting with '%' is used. :rtype: STRING When the *format* argument is assigned, the string is interpreted according to the specified language. When the *format* argument specified is not corresponding to the given string, an error is returned. In the following :ref:`Date/Time Format 2 ` table, the month/day, date, and AM/PM in characters are different by language. .. _datetime-format2: **Date/Time Format 2** +------------------+-------------------------------------------------------------------------------------------------------------------+ | format Value | Meaning | +==================+===================================================================================================================+ | %a | Weekday, English abbreviation (Sun, ... , Sat) | +------------------+-------------------------------------------------------------------------------------------------------------------+ | %b | Month, English abbreviation (Jan, ... , Dec) | +------------------+-------------------------------------------------------------------------------------------------------------------+ | %c | Month (1, ... , 12) | +------------------+-------------------------------------------------------------------------------------------------------------------+ | %D | Day of the month, English ordinal number (1st, 2nd, 3rd, ...) | +------------------+-------------------------------------------------------------------------------------------------------------------+ | %d | Day of the month, two-digit number (01, ... , 31) | +------------------+-------------------------------------------------------------------------------------------------------------------+ | %e | Day of the month (1, ... , 31) | +------------------+-------------------------------------------------------------------------------------------------------------------+ | %f | Microseconds, three-digit number (000, ... , 999) | +------------------+-------------------------------------------------------------------------------------------------------------------+ | %H | Hour, 24-hour based, number with at least two--digit (00, ... , 23, ... , 100, ... ) | +------------------+-------------------------------------------------------------------------------------------------------------------+ | %h | Hour, 12-hour based two-digit number (01, ... , 12) | +------------------+-------------------------------------------------------------------------------------------------------------------+ | %I | Hour, 12-hour based two-digit number (01, ... , 12) | +------------------+-------------------------------------------------------------------------------------------------------------------+ | %i | Minutes, two-digit number (00, ... , 59) | +------------------+-------------------------------------------------------------------------------------------------------------------+ | %j | Day of year, three-digit number (001, ... , 366) | +------------------+-------------------------------------------------------------------------------------------------------------------+ | %k | Hour, 24-hour based, number with at least one-digit (0, ... , 23, ... , 100, ... ) | +------------------+-------------------------------------------------------------------------------------------------------------------+ | %l | Hour, 12-hour based (1, ... , 12) | +------------------+-------------------------------------------------------------------------------------------------------------------+ | %M | Month, English string (January, ... , December) | +------------------+-------------------------------------------------------------------------------------------------------------------+ | %m | Month, two-digit number (01, ... , 12) | +------------------+-------------------------------------------------------------------------------------------------------------------+ | %p | AM or PM | +------------------+-------------------------------------------------------------------------------------------------------------------+ | %r | Time, 12-hour based, hour:minute:second (hh:mi:ss AM or hh:mi:ss PM) | +------------------+-------------------------------------------------------------------------------------------------------------------+ | %S | Seconds, two-digit number (00, ... , 59) | +------------------+-------------------------------------------------------------------------------------------------------------------+ | %s | Seconds, two-digit number (00, ... , 59) | +------------------+-------------------------------------------------------------------------------------------------------------------+ | %T | Time, 24-hour based, hour:minute:second (hh:mi:ss) | +------------------+-------------------------------------------------------------------------------------------------------------------+ | %U | Week, two-digit number, week number of the year with Sunday being the first day Week (00, ... , 53) | +------------------+-------------------------------------------------------------------------------------------------------------------+ | %u | Week, two-digit number, week number of the year with Monday being the first day (00, ... , 53) | +------------------+-------------------------------------------------------------------------------------------------------------------+ | %V | Week, two-digit number, week number of the year with Sunday being the first day Week (00, ... , 53) | | | (Available to use in combination with %X) | +------------------+-------------------------------------------------------------------------------------------------------------------+ | %v | Week, two-digit number, week number of the year with Monday being the first day (00, ... , 53) | | | (Available to use in combination with %x) | +------------------+-------------------------------------------------------------------------------------------------------------------+ | %W | Weekday, English string (Sunday, ... , Saturday) | +------------------+-------------------------------------------------------------------------------------------------------------------+ | %w | Day of the week, number index (0=Sunday, ... , 6=Saturday) | +------------------+-------------------------------------------------------------------------------------------------------------------+ | %X | Year, four-digit number calculated as the week number with Sunday being the first day of the week | | | (0000, ... , 9999) (Available to use in combination with %V)   | +------------------+-------------------------------------------------------------------------------------------------------------------+ | %x | Year, four-digit number calculated as the week number with Monday being the first day of the week | | | (0000, ... , 9999) (Available to use in combination with %v) | +------------------+-------------------------------------------------------------------------------------------------------------------+ | %Y | Year, four-digit number (0001, ... , 9999) | +------------------+-------------------------------------------------------------------------------------------------------------------+ | %y | Year, two-digit number (00, 01, ... ,  99) | +------------------+-------------------------------------------------------------------------------------------------------------------+ | %% | Output the special character "%" as a string | +------------------+-------------------------------------------------------------------------------------------------------------------+ | %x | Output an arbitrary character x as a string out of English letters that are not used as format specifiers. | +------------------+-------------------------------------------------------------------------------------------------------------------+ .. code-block:: sql SELECT DATE_FORMAT('2009-10-04 22:23:00', '%W %M %Y'); :: date_format('2009-10-04 22:23:00', '%W %M %Y') ====================== 'Sunday October 2009' .. code-block:: sql SELECT DATE_FORMAT('2007-10-04 22:23:00', '%H:%i:%s'); :: date_format('2007-10-04 22:23:00', '%H:%i:%s') ====================== '22:23:00' .. code-block:: sql SELECT DATE_FORMAT('1900-10-04 22:23:00', '%D %y %a %d %m %b %j'); :: date_format('1900-10-04 22:23:00', '%D %y %a %d %m %b %j') ====================== '4th 00 Thu 04 10 Oct 277' .. code-block:: sql SELECT DATE_FORMAT('1999-01-01', '%X %V'); :: date_format('1999-01-01', '%X %V') ====================== '1998 52' FORMAT ====== .. function:: FORMAT ( x , dec ) The **FORMAT** function displays the number *x* by using digit grouping symbol as thousands separator, so that its format becomes '#,###,###.#####' and performs rounding after the decimal point symbol to express as many as *dec* digits after it. The return value is a **VARCHAR** type. :param x: An expression that returns a numeric value :param dec: the number of digits of fractional parts :rtype: STRING Thousands separator and decimal point symbol are output in the specified format. .. code-block:: sql SELECT FORMAT(12000.123456,3), FORMAT(12000.123456,0); :: format(12000.123456, 3) format(12000.123456, 0) ============================================ '12,000.123' '12,000' STR_TO_DATE =========== .. function:: STR_TO_DATE (string, format) The **STR_TO_DATE** function converts the given character string to a date/time value by interpreting it according to the specified format and operates in the opposite way to the :func:`DATE_FORMAT` function. The return value is determined by the date/time part included in the character string and it is one of the **DATETIME**, **DATE** and **TIME** types. :param string: All character string types can be specified. :param format: Specifies the format to interpret the character string. You should use character strings including % for the format specifiers. See :ref:`Date/Time Format 2 ` table of :func:`DATE_FORMAT` function. :rtype: DATETIME, DATE, TIME For the *format* argument to assign, see :ref:`Date/Time Format 2 ` table of the :func:`DATE_FORMAT`. If *string* is invalid date/time value or *format* is invalid, it returns an error. When the *format* argument is assigned, the *string* is interpreted according to the specified format. When the *format* argument specified is not corresponding to the given *string*, an error is returned. 0 is not allowed in the argument value corresponding to year, month, and day; however, if 0 is inputted in every argument value corresponding to date and time, the value of **DATE** or **DATETIME** type that has 0 for every date and time value is returned as an exception. Note that operation in JDBC program is determined by the configuration of zeroDateTimeBehavior, connection URL property. For more information about zeroDateTimeBehavior, please refer :ref:`jdbc-connection-conf`. .. code-block:: sql SELECT STR_TO_DATE('01,5,2013','%d,%m,%Y'); :: str_to_date('01,5,2013', '%d,%m,%Y') ======================================= 05/01/2013 .. code-block:: sql SELECT STR_TO_DATE('May 1, 2013','%M %d,%Y'); :: str_to_date('May 1, 2013', '%M %d,%Y') ========================================= 05/01/2013 .. code-block:: sql SELECT STR_TO_DATE('13:30:17','%H:%i'); :: str_to_date('13:30:17', '%H:%i') ======================================== 01:30:00 PM .. code-block:: sql SELECT STR_TO_DATE('09:30:17 PM','%r'); :: str_to_date('09:30:17 PM', '%r') ======================================= 09:30:17 PM .. code-block:: sql SELECT STR_TO_DATE('0,0,0000','%d,%m,%Y'); :: str_to_date('0,0,0000', '%d,%m,%Y') ====================================== 00/00/0000 TIME_FORMAT =========== .. function:: TIME_FORMAT (time, format) The **TIME_FORMAT** function converts the value of strings with **TIME** format ('*HH*-*MI*-*SS)* or that of date/time data type (**DATE**, **TIMESTAMP**, **DATETIME**) to specified date/time format and then return the value with the **VARCHAR** data type. :param time: A value of string with **TIME** (*HH*:*MI*:*SS*) or that of date/time data type (**TIME**, **TIMESTAMP**, **DATETIME**) can be specified. :param format: Specifies the output format. Use a string that contains '%' as a specifier. See the table :ref:`Date/Time Format 2 ` of :func:`DATE_FORMAT` function. :rtype: STRING When the *format* argument is assigned, the time is output according to the specified format. When the specified *format* argument does not correspond to the given string, an error is returned. .. code-block:: sql SELECT TIME_FORMAT('22:23:00', '%H %i %s'); :: time_format('22:23:00', '%H %i %s') ====================== '22 23 00' .. code-block:: sql SELECT TIME_FORMAT('23:59:00', '%H %h %i %s %f'); :: time_format('23:59:00', '%H %h %i %s %f') ====================== '23 11 59 00 000' .. code-block:: sql SELECT SYSTIME, TIME_FORMAT(SYSTIME, '%p'); :: SYS_TIME time_format( SYS_TIME , '%p') =================================== 08:46:53 PM 'PM' TO_CHAR(date_time) ================== .. function:: TO_CHAR ( date_time [, format[, date_lang_string_literal ]] ) The **TO_CHAR** (date_time) function converts the value of date/time types (**TIME**, **DATE**, **TIMESTAMP**, **DATETIME**) to a string depending on the table :ref:`Date/Time Format 1 ` and then returns the value. The type of the return value is **VARCHAR**. :param date_time: Specifies an expression that returns date-time type string. If the value is **NULL**, **NULL** is returned. :param format: Specifies a format of return value. If the value is **NULL**, **NULL** is returned. :param date_lang_string_literal: Specifies a language applied to a return value(see :ref:`date_lang_string_literal table `). 'en_US' or 'ko_KR' can be used. You can modify the value by specifying the CUBRID_DATE_LANG environment variable. :rtype: STRING When the *format* argument is assigned, the *date_time* is output according to the specified format (see the :ref:`Date/Time Format 1 ` table). When the *format* argument specified does not correspond to the given *string*, an error is returned. .. _tochar-default-datetime-format: **Default Date/Time Output Format** +----------------+---------------+---------------------------+------------------------------+ | DATE | TIME | TIMESTAMP | DATETIME | +================+===============+===========================+==============================+ | 'MM/DD/YYYY' | 'HH:MI:SS AM' | 'HH:MI:SS AM MM/DD/YYYY' | 'HH:MI:SS.FF AM MM/DD/YYYY' | +----------------+---------------+---------------------------+------------------------------+ .. _datetime-format1: **Date/Time Format 1** +--------------------+---------------------------------------------------------------------------+ | Format Element | Description | +====================+===========================================================================+ | **CC** | Century | +--------------------+---------------------------------------------------------------------------+ | **YYYY** | Year with 4 numbers, Year with 2 numbers | | , **YY** | | +--------------------+---------------------------------------------------------------------------+ | **Q** | Quarter (1, 2, 3, 4; January - March = 1) | +--------------------+---------------------------------------------------------------------------+ | **MM** | Month (01-12; January = 01) | | | *Note: MI represents the minute of hour.* | +--------------------+---------------------------------------------------------------------------+ | **MONTH** | Month in characters | +--------------------+---------------------------------------------------------------------------+ | **MON** | Abbreviated month name | +--------------------+---------------------------------------------------------------------------+ | **DD** | Day (1 - 31) | +--------------------+---------------------------------------------------------------------------+ | **DAY** | Day of the week in characters | +--------------------+---------------------------------------------------------------------------+ | **DY** | Abbreviated day of the week | +--------------------+---------------------------------------------------------------------------+ | **D** or **d** | Day of the week in numbers (1 - 7) | +--------------------+---------------------------------------------------------------------------+ | **AM** or **PM** | AM/PM | +--------------------+---------------------------------------------------------------------------+ | **A.M.** | AM/PM with periods | | or **P.M.** | | +--------------------+---------------------------------------------------------------------------+ | **HH** | Hour (1 -12) | | or **HH12** | | +--------------------+---------------------------------------------------------------------------+ | **HH24** | Hour (0 - 23) | +--------------------+---------------------------------------------------------------------------+ | **MI** | Minute (0 - 59) | +--------------------+---------------------------------------------------------------------------+ | **SS** | Second (0 - 59) | +--------------------+---------------------------------------------------------------------------+ | **FF** | Millisecond (0-999) | +--------------------+---------------------------------------------------------------------------+ | \- / , . ; : "text"| Punctuation and quotation marks are represented as they are in the result | +--------------------+---------------------------------------------------------------------------+ .. _date_lang_string_literal-format: **A table of date_lang_string_literal** +--------------+--------------------------------------------+ | **Format | **date_lang_string_literal** | | Element** +------------------------------+-------------+ | | **'en_US'** | **'ko_KR'** | +==============+==============================+=============+ | **MONTH** | JANUARY | 1월 | +--------------+------------------------------+-------------+ | **MON** | JAN | 1 | +--------------+------------------------------+-------------+ | **DAY** | MONDAY | 월요일 | +--------------+------------------------------+-------------+ | **DY** | MON | 월 | +--------------+------------------------------+-------------+ | **Month** | January | 1월 | +--------------+------------------------------+-------------+ | **Mon** | Jan | 1 | +--------------+------------------------------+-------------+ | **Day** | Monday | 월요일 | +--------------+------------------------------+-------------+ | **Dy** | Mon | 월 | +--------------+------------------------------+-------------+ | **month** | january | 1월 | +--------------+------------------------------+-------------+ | **mon** | jan | 1 | +--------------+------------------------------+-------------+ | **day** | monday | 월요일 | +--------------+------------------------------+-------------+ | **Dy** | mon | 월 | +--------------+------------------------------+-------------+ | **AM** | AM | 오전 | +--------------+------------------------------+-------------+ | **Am** | Am | 오전 | +--------------+------------------------------+-------------+ | **am** | am | 오전 | +--------------+------------------------------+-------------+ | **A.M.** | A.M. | 오전 | +--------------+------------------------------+-------------+ | **A.m.** | A.m. | 오전 | +--------------+------------------------------+-------------+ | **a.m.** | a.m. | 오전 | +--------------+------------------------------+-------------+ | **PM** | PM | 오후 | +--------------+------------------------------+-------------+ | **Pm** | Pm | 오후 | +--------------+------------------------------+-------------+ | **pm** | pm | 오후 | +--------------+------------------------------+-------------+ | **P.M.** | P.M. | 오후 | +--------------+------------------------------+-------------+ | **P.m.** | P.m. | 오후 | +--------------+------------------------------+-------------+ | **p.m.** | p.m. | 오후 | +--------------+------------------------------+-------------+ **Example of Format Digits of Return Value** +-------------------------+---------------------------------------------------------------------+ | Format Element | en_US Digits | ko_KR Digits | +=========================+==================================+==================================+ | **MONTH(Month, month)** | 9 | 4 | +-------------------------+----------------------------------+----------------------------------+ | **MON(Mon, mon)** | 3 | 2 | +-------------------------+----------------------------------+----------------------------------+ | **DAY(Day, day)** | 9 | 6 | +-------------------------+----------------------------------+----------------------------------+ | **DY(Dy, dy)** | 3 | 2 | +-------------------------+----------------------------------+----------------------------------+ | **HH12, HH24** | 2 | 2 | +-------------------------+----------------------------------+----------------------------------+ | "text" | The length of the text | The length of the text | +-------------------------+----------------------------------+----------------------------------+ | Other formats | Same as the length of the format | Same as the length of the format | +-------------------------+----------------------------------+----------------------------------+ .. code-block:: sql --creating a table having date/time type columns CREATE TABLE datetime_tbl(a TIME, b DATE, c TIMESTAMP, d DATETIME); INSERT INTO datetime_tbl VALUES(SYSTIME, SYSDATE, SYSTIMESTAMP, SYSDATETIME); --selecting a VARCHAR type string from the data in the specified format SELECT TO_CHAR(b, 'DD, DY , MON, YYYY') FROM datetime_tbl; :: to_char(b, 'DD, DY , MON, YYYY') ====================== '04, THU , FEB, 2010' .. code-block:: sql SELECT TO_CHAR(c, 'HH24:MI, DD, MONTH, YYYY') FROM datetime_tbl; :: to_char(c, 'HH24:MI, DD, MONTH, YYYY') ====================== '16:50, 04, FEBRUARY , 2010' .. code-block:: sql SELECT TO_CHAR(d, 'HH12:MI:SS:FF pm, YYYY-MM-DD-DAY') FROM datetime_tbl; :: to_char(d, 'HH12:MI:SS:FF pm, YYYY-MM-DD-DAY') ====================== '04:50:11:624 pm, 2010-02-04-THURSDAY ' .. code-block:: sql SELECT TO_CHAR(TIMESTAMP'2009-10-04 22:23:00', 'Day Month yyyy'); :: to_char(timestamp '2009-10-04 22:23:00', 'Day Month yyyy') ====================== 'Sunday October 2009' TO_CHAR(number) =============== .. function:: TO_CHAR(number[, format]) The **TO_CHAR** function converts a numeric data type to a character string according to :ref:`Number Format ` and returns it. The type of the return value is **VARCHAR** . :param number: Specifies an expression that returns numeric data type string. If the input value is **NULL**, **NULL** is returned. If the input value is character type, the character itself is returned. :param format: Specifies a format of return value. If format is not specified, all significant figures are returned as character string by default. If the value is **NULL**, **NULL** is returned. :rtype: STRING .. _tochar-number-format: **Number Format** +--------------------+-------------+------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+ | Format Element | Example | Description | +====================+=============+==========================================================================================================================================================================================+ | **9** | 9999 | The number of 9's represents the number of significant figures to be returned. | | | | If the number of significant figures specified in the format is not sufficient, only the decimal part is rounded. If it is less than the number of digits in an integer, # is outputted. | | | | If the number of significant figures specified in the format is sufficient, the part preceding the integer part is filled with space characters and the decimal part is filled with 0. | +--------------------+-------------+------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+ | **0** | 0999 | If the number of significant figures specified in the format is sufficient, the part preceding the integer part is filled with 0, not space characters before the value is returned. | +--------------------+-------------+------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+ | **S** | S9999 | Outputs the negative/positive sign in the specified position. These signs can be used only at the beginning of character string. | +--------------------+-------------+------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+ | **C** | C9999 | Returns the ISO currency code at the specified position. | +--------------------+-------------+------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+ | **,** | 9,999 | Returns a comma (",") at the specified position. | | (comma) | | | +--------------------+-------------+------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+ | **.** | 9.999 | Returns a decimal point (".") which distinguishes between a decimal and an at the specified position. | | (decimal point) | | | +--------------------+-------------+------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+ | **EEEE** | 9.99EEEE | Returns a scientific notation number. | +--------------------+-------------+------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+ .. code-block:: sql --selecting a string casted from a number in the specified format SELECT TO_CHAR(12345,'S999999'), TO_CHAR(12345,'S099999'); :: to_char(12345, 'S999999') to_char(12345, 'S099999') ============================================ ' +12345' '+012345' .. code-block:: sql SELECT TO_CHAR(1234567,'9,999,999,999'); :: to_char(1234567, '9,999,999,999') ====================== ' 1,234,567' .. code-block:: sql SELECT TO_CHAR(1234567,'9.999.999.999'); :: to_char(1234567, '9.999.999.999') ====================== '##############' .. code-block:: sql SELECT TO_CHAR(123.4567,'99'), TO_CHAR(123.4567,'999.99999'), TO_CHAR(123.4567,'99999.999'); :: to_char(123.4567, '99') to_char(123.4567, '999.99999') to_char(123.4567, '99999.999') ================================================================== '##' '123.45670' ' 123.457' TO_DATE ======= .. function:: TO_DATE(string [,format [,date_lang_string_literal]]) The **TO_DATE** function interprets a character string based on the date format given as an argument, converts it to a **DATE** type value, and returns it. For the format, see :ref:`Date/Time Format 1 `. :param string: Specifies an expression that returns character string. If the value is **NULL**, **NULL** is returned. :param format: Specifies a format of return value to be converted as **DATE** type. See :ref:`Date/Time Format 1 `. If the value is **NULL**, **NULL** is returned. :param date_lang_string_literal: Specifies the language for the input value to be applied. 'en_US' or 'ko_KR' can be used. You can modify the value by using the CUBRID_DATE_LANG environment. :rtype: DATE When the *format* argument is assigned, the *string* is interpreted according to the specified format. When the *format* parameter specified does not correspond to the given *string*, an error is returned. .. code-block:: sql --selecting a date type value casted from a string in the specified format SELECT TO_DATE('12/25/2008'); :: to_date('12/25/2008') =============================================== 12/25/2008 .. code-block:: sql SELECT TO_DATE('25/12/2008', 'DD/MM/YYYY'); :: to_date('25/12/2008', 'DD/MM/YYYY') =============================================== 12/25/2008 .. code-block:: sql SELECT TO_DATE('081225', 'YYMMDD'); :: to_date('081225', 'YYMMDD', 'en_US') =============================================== 12/25/2008 .. code-block:: sql SELECT TO_DATE('2008-12-25', 'YYYY-MM-DD'); :: to_date('2008-12-25', 'YYYY-MM-DD', 'en_US') =============================================== 12/25/2008 TO_DATETIME =========== .. function:: TO_DATETIME (string [,format [,date_lang_string_literal]]) The **TO_DATETIME** function interprets a character string based on the date-time format given as an argument, converts it to a **DATETIME** type value, and returns it. For the format, see :ref:`Date/Time Format 1 `. :param string: Specifies an expression that returns character string. If the value is **NULL**, **NULL** is returned. :param format: Specifies a format of return value to be converted as **DATETIME** type. See the table, :ref:`Date/Time Format 1 `. If the value is **NULL**, **NULL** is returned. :param date_lang_string_literal: Specifies the language for the input value to be applied. 'en_US' or 'ko_KR' can be used. You can modify the value by specifying the CUBRID_DATE_LANG environment variable. :rtype: DATETIME When the *format* argument is assigned, the *string* is interpreted according to the specified format. When the *format* parameter specified does not correspond to the given *string*, an error is returned. .. code-block:: sql --selecting a datetime type value casted from a string in the specified format SELECT TO_DATETIME('13:10:30 12/25/2008'); :: to_datetime('13:10:30 12/25/2008') ===================================== 01:10:30.000 PM 12/25/2008 .. code-block:: sql SELECT TO_DATETIME('08-Dec-25 13:10:30.999', 'YY-Mon-DD HH24:MI:SS.FF'); :: to_datetime('08-Dec-25 13:10:30.999', 'YY-Mon-DD HH24:MI:SS.FF') ===================================== 01:10:30.999 PM 12/25/2008 .. code-block:: sql SELECT TO_DATETIME('DATE: 12-25-2008 TIME: 13:10:30.999', '"DATE:" MM-DD-YYYY "TIME:" HH24:MI:SS.FF'); :: to_datetime('DATE: 12-25-2008 TIME: 13:10:30.999', '"DATE:" MM-DD-YYYY "TIME:" HH24:MI:SS.FF') ===================================== 01:10:30.999 PM 12/25/2008 TO_NUMBER ========= .. function:: TO_NUMBER(string [, format ]) The **TO_NUMBER** function interprets a character string based on the number format given as an argument, converts it to a **NUMERIC** type value, and returns it. :param string: Specifies an expression that returns character string. If the value is **NULL**, **NULL** is returned. :param format: Specifies a format of return value to be converted as **NUMBER** type. See :ref:`Number Format `. If the value is **NULL**, an error is returned. :rtype: NUMERIC When the *format* argument is assigned, the string is interpreted according to the specified format. .. code-block:: sql --selecting a number casted from a string in the specified format SELECT TO_NUMBER('-1234'); :: to_number('-1234') ============================================ -1234 .. code-block:: sql SELECT TO_NUMBER('12345','999999'); :: to_number('12345', '999999') ============================================ 12345 .. code-block:: sql SELECT TO_NUMBER('12,345.67','99,999.999'); :: to_number('12,345.67', '99,999.999') ====================== 12345.670 .. code-block:: sql SELECT TO_NUMBER('12345.67','99999.999'); :: to_number('12345.67', '99999.999') ============================================ 12345.670 TO_TIME ======= .. function:: TO_TIME (string [,format [,date_lang_string_literal]]) The **TO_TIME** function interprets a character string based on the time format given as an argument, converts it to a **TIME** type value, and returns it. For the format, see :ref:`Date/Time Format 1 `. :param string: Specifies an expression that returns character string. If the value is **NULL**, **NULL** is returned. :param format: Specifies a format of return value to be converted as **TIME** type. See :ref:`Date/Time Format 1 `. If the value is **NULL**, **NULL** is returned. :param date_lang_string_literal: Specifies the language for the input value to be applied. 'en_US' or 'ko_KR' can be used. You can modify the value by specifying the CUBRID_DATE_LANG environment variable. :rtype: TIME When the *format* argument is assigned, the *string* is interpreted according to the specified format. When the *format* parameter specified does not correspond to the given *string*, an error is returned. .. code-block:: sql --selecting a time type value casted from a string in the specified format SELECT TO_TIME ('13:10:30'); :: to_time('13:10:30') ============================================= 01:10:30 PM .. code-block:: sql SELECT TO_TIME('HOUR: 13 MINUTE: 10 SECOND: 30', '"HOUR:" HH24 "MINUTE:" MI "SECOND:" SS'); :: to_time('HOUR: 13 MINUTE: 10 SECOND: 30', '"HOUR:" HH24 "MINUTE:" MI "SECOND:" SS', 'en_US') ============================================= 01:10:30 PM .. code-block:: sql SELECT TO_TIME ('13:10:30', 'HH24:MI:SS'); :: to_time('13:10:30', 'HH24:MI:SS') ============================================= 01:10:30 PM .. code-block:: sql SELECT TO_TIME ('13:10:30', 'HH12:MI:SS'); :: ERROR: Conversion error in date format. TO_TIMESTAMP ============ .. function:: TO_TIMESTAMP(string [, format [,date_lang_string_literal]]) The **TO_TIMESTAMP** function interprets a character string based on the time format given as an argument, converts it to a **TIMESTAMP** type value, and returns it. For the format, see :ref:`Date/Time Format 1 `. :param string: Specifies an expression that returns character string. If the value is **NULL**, **NULL** is returned. :param format: Specifies a format of return value to be converted as **TIMESTAMP** type. See :ref:`Date/Time Format 1 `. If the value is **NULL**, **NULL** is returned. :param date_lang_string_literal: Specifies the language for the input value to be applied. 'en_US' or 'ko_KR' can be used. You can modify the value by specifying the CUBRID_DATE_LANG environment variable. :rtype: TIMESTAMP When the *format* argument is assigned, the *string* is interpreted according to the specified format. When the *format* parameter specified does not correspond to the given string, an error is returned. .. code-block:: sql --selecting a timestamp type value casted from a string in the specified format SELECT TO_TIMESTAMP('13:10:30 12/25/2008'); :: to_timestamp('13:10:30 12/25/2008') ====================================== 01:10:30 PM 12/25/2008 .. code-block:: sql SELECT TO_TIMESTAMP('08-Dec-25 13:10:30', 'YY-Mon-DD HH24:MI:SS'); :: to_timestamp('08-Dec-25 13:10:30', 'YY-Mon-DD HH24:MI:SS') ====================================== 01:10:30 PM 12/25/2008 .. code-block:: sql SELECT TO_TIMESTAMP('YEAR: 2008 DATE: 12-25 TIME: 13:10:30', '"YEAR:" YYYY "DATE:" MM-DD "TIME:" HH24:MI:SS'); :: to_timestamp('YEAR: 2008 DATE: 12-25 TIME: 13:10:30', '"YEAR:" YYYY "DATE:" MM-DD "TIME:" HH24:MI:SS') ====================================== 01:10:30 PM 12/25/2008